CREATE PROCEDURE RRHH_BUSCAR_DISPONIBILIDAD_EMPLEADO_L
@IDEMPLEADO INTEGER,
@HORA DATETIME,
@FECHA DATETIME,
@IDSERVICIO INTEGER,
@ESTADOTRAN INTEGER OUTPUT
AS

BEGIN TRANSACTION

DECLARE @IDES INTEGER
DECLARE @IDAC INTEGER
DECLARE @DURA INTEGER
DECLARE @CONT INTEGER
DECLARE @HORAFIN DATETIME

BEGIN TRY
	SET @IDES = (SELECT IDESTADO FROM ESTADOEMPLEADO
				WHERE descripcion = 'Eliminado')
	SET @DURA = (SELECT duracionHoras FROM SERVICIO
				 WHERE idServicio = @IDSERVICIO)
	SET @HORAFIN = @HORA
	
	SET @HORAFIN = DATEADD(HOUR, @DURA, @HORAFIN)
	
	SET @CONT = (SELECT COUNT(*) FROM EMPLEADOXSERVICIO
				 WHERE idEmpleado = @IDEMPLEADO AND idServicio = @IDSERVICIO)
	
	IF @CONT = 0
	BEGIN
		SET @ESTADOTRAN = 2
		select @ESTADOTRAN
		COMMIT TRANSACTION
	END

		SET @CONT = (SELECT COUNT(*) FROM AGENDAACTIVIDAD
					 WHERE idEmpleado = @IDEMPLEADO AND CAST(fecha AS DATE) = CAST(@FECHA AS DATE) 
					 AND
					 (
					 ((CAST(@HORA AS TIME(7)) >= CAST(horaInicioAct AS TIME(7)))
					 AND (CAST(@HORA AS TIME(7)) <= CAST(horaFinActividad AS TIME(7))) 
					 AND (CAST(@HORAFIN AS TIME(7)) >= CAST(horaInicioAct AS TIME(7))) 
					 AND (CAST(@HORAFIN AS TIME(7)) <= CAST(horaFinActividad AS TIME(7))))
					 OR
					 ((CAST(@HORA AS TIME(7)) > CAST(horaInicioAct AS TIME(7))) 
					 AND (CAST(@HORA AS TIME(7)) < CAST(horaFinActividad AS TIME(7))) 
					 AND (CAST(@HORAFIN AS TIME(7)) > CAST(horaFinActividad AS TIME(7))))
		       		 OR
					 ((CAST(@HORA AS TIME(7)) < CAST(horaInicioAct AS TIME(7))) 
					 AND (CAST(@HORAFIN AS TIME(7)) > CAST(horaInicioAct AS TIME(7)))
					 AND (CAST(@HORAFIN AS TIME(7)) < CAST(horaFinActividad AS TIME(7))))
					 OR
					 ((CAST(@HORA AS TIME(7)) < CAST(horaInicioAct AS TIME(7))) 
					 AND (CAST(@HORAFIN AS TIME(7)) > CAST(horaFinActividad AS TIME(7)))) 
					 )
					 AND ESTADO = 1
					 )
					 	
		IF @CONT = 0
		BEGIN
			-- el empleado esta libre
			SET @ESTADOTRAN = 1
			select @ESTADOTRAN
			COMMIT TRANSACTION
		END
		ELSE
		BEGIN
			-- el empleado esta ocupado
			SET @ESTADOTRAN = 0
			select @ESTADOTRAN
			COMMIT TRANSACTION
		END
	
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH